//
//  SISQLiteManager.swift
//  SinaImiate
//
//  Created by xiaoming on 16/7/14.
//  Copyright © 2016年 GoodJob. All rights reserved.
//

import Foundation
import FMDB

private let maxDBCacheTime: TimeInterval = -60 //-5 * 24 * 60 * 60

/// SQL管理器
class SISQLiteManager {
    
    static let shared = SISQLiteManager()
    
    let queue: FMDatabaseQueue
    
    private init() {
        
        let dbName = "status.db"
        
        var path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        ///数据库路径
        path = (path as NSString).appendingPathComponent(dbName)
        ///创建或者打开数据库
        queue = FMDatabaseQueue(path: path)
        
        createTable()
        
        NotificationCenter.default.addObserver(self, selector: #selector(clearDBCache), name: NSNotification.Name.UIApplicationDidEnterBackground, object: nil)
    }
    
   
    deinit {
        NotificationCenter.default.removeObserver(self)
    }
    ///数据缓存实现
    @objc private func clearDBCache() {
        
        let dateString = Date.cz_dateString(delta: maxDBCacheTime)
        
        print("清理数据缓存\(dateString)")
        
        let sql = "DELETE FROM T_Status WHERE creatTime < ?"
        
        queue.inDatabase { (db) in
            
            if db?.executeUpdate(sql, withArgumentsIn: [dateString]) == true {
                
                print("删除了 \(db?.changes()) 条记录")
            }
            
        }
    }
    
}

extension SISQLiteManager {
    
    /// 从数据库加载微博数据数组
    func loadStatus(userId: String, since_id: Int64 = 0, max_id: Int64 = 0) -> [[String: AnyObject]] {
        
        var sql = "SELECT statusId, userId, status FROM T_Status \n"
        
        sql += "WHERE userId = \(userId)\n"
        
        if since_id > 0 {
            sql += "AND statusId > \(since_id)\n"
        } else if max_id > 0 {
            sql += "AND statusId < \(max_id)\n"
        }
        
        sql += "ORDER BY statusId DESC LIMIT 20;"
        
        let array = execRecordSet(sql: sql)
        
        var result = [[String: AnyObject]]()
        
        for dict in array {
            
            guard let jsonData = dict["status"] as? Data,
                json = try? JSONSerialization.jsonObject(with: jsonData, options: []) as! [String: AnyObject]
                else {
                    continue
            }
            
            result.append(json ?? [:])
            
        }
        
        return result
        
    }
    
    /// 新增或者修改微博数据, 微博数据在刷新的时候, 可能会出现重叠
    ///
    func updateStatus(userId: String, array: [[String: AnyObject]]) {
        
        let sql = "INSERT OR REPLACE INTO T_Status (statusId, userId, status) VALUES (?, ?, ?);"
        queue.inTransaction { (db, rollback) in
            
            for dict in array {
                
                guard let statusId = dict["idstr"] as? String,
                    jsonData = try? JSONSerialization.data(withJSONObject: dict, options: [])
                    else {
                        
                        continue
                }
                
                if db?.executeUpdate(sql, withArgumentsIn: [statusId, userId, jsonData]) == false {
                    
                    rollback?.pointee = true
                    
                    break
                }
                
            }
            
        }
    }
    
    
}

extension SISQLiteManager {
    
    /// 执行sql返回查询到的字典数组
    ///
    func execRecordSet(sql: String) -> [[String: AnyObject]] {
        
        var result = [[String: AnyObject]]()
        
        queue.inDatabase { (db) in
            
            guard let rs = db?.executeQuery(sql, withArgumentsIn: []) else {
                
                return
            }
            
            while rs.next() {
                
                let colCount = rs.columnCount()
                
                for col in 0..<colCount {
                    
                    guard let name = rs.columnName(for: col),
                        value = rs.object(forColumnIndex: col)
                        else {
                            continue
                    }
                    
                    result.append([name: value])
                    
                }
                
            }
            
        }
        return result
    }
    
    /// 创建数据库表
    func createTable() {
        
        guard let path = Bundle.main.pathForResource("status.sql", ofType: nil),
            sql = try? String(contentsOfFile: path)
            else {
                return
        }
        
        queue.inDatabase { (db) in
            
            if db?.executeStatements(sql) == true {
                print("创表成功")
            } else {
                print("创表失败")
            }
        }
    }
}
